<?php
/**
 * product class. Provide all utilities function related to 
 * product table.
 * <u>Note:</u> all $db param in first parameter is ezMySQL object.
 * See ezSQL documentation for more information.
 */
class FIN_productBean 
{
   
   function getAllProduct_home($db,$langid,$limit,$type)
    {
    	$sql = sprintf("SELECT cl.cat_name,cl.cat_id, p.*,FORMAT(p.price,0) as price,pl.short_info,pl.product_name ".
				" FROM ".TBL_PRODUCT." p ".
				" LEFT JOIN ".TBL_CATEGORY." c on p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." cl ON cl.cat_id = c.cat_ID AND cl.lang_id = '$langid'". 
				" LEFT JOIN ".TBL_PRODUCT_LANG." pl ON pl.product_id = p.product_id AND pl.lang_id = '$langid' ".
				" WHERE p.status = '$type' ".
				" ORDER BY p.product_id LIMIT 0,".$limit."");
    	$res = $db->get_results($sql);
    	
    	return $res;
    }
    
	function count_product($db, $id,$langid,$status,$productid){
		if($id!="")
			$cond = " AND c.cat_ID='$id'";
		if($status!="")
			$cond_2 = " AND p.status 	='$status'";
		if($productid!="")
			$cond_3 = " AND p.product_id!='$productid'";	
    	$sql = "SELECT COUNT(p.product_id) as total FROM ".TBL_PRODUCT." p ".
				"LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$langid' ".
				"WHERE l.lang_id = '$langid' $cond $cond_2 $cond_3";
    	$res = $db->get_row($sql);
    	
    	return $res->total;
    } 
	
	function getProductsByCategory($db, $id,$langid,$share,$path,$status,$productid){
		if($id!="")
			$cond = " AND c.cat_ID = '$id'";
		if($status!="")
			$cond_2 = " AND p.status 	='$status'";
		if($productid!="")
			$cond_3 = " AND p.product_id!='$productid'";		
		
		$page = isset ( $_GET[$path] ) ? intval ( $_GET[$path] ) : 1;
		$rows_per_page= $share;
		$page_start = ( $page - 1 ) * $rows_per_page;
		$page_end = $rows_per_page;
    	$sql = sprintf("SELECT *,FORMAT(p.price,0) as price FROM ".TBL_PRODUCT." p ".
				"LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$langid' ".
				" LEFT JOIN ".TBL_PRODUCT_LANG." t ON t.product_id = p.product_id AND t.lang_id = '$langid' ".
				"WHERE p.product_id != 'NULL' $cond $cond_2 $cond_3 ORDER BY t.product_name  LIMIT $page_start, $page_end");
    	$ressult = $db->get_results($sql);
    	return $ressult;
    }
    
	function getAllProductsSearch($db ,$txtKey,$langid)
    {
    	$sql = "SELECT COUNT(p.product_id) as total FROM ".TBL_PRODUCT." p LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$langid' ".
				" LEFT JOIN ".TBL_PRODUCT_LANG." t ON t.product_id = p.product_id AND t.lang_id = '$langid' ".
				"WHERE t.product_name like '%$txtKey%'";
    			
    	$res = $db->get_row($sql);
    	return $res->total;
    }
    
    function getAllProductsSearchSharePage($db ,$txtKey,$langid,$share,$path)
    {
    	$page = isset ( $_GET[$path] ) ? intval ( $_GET[$path] ) : 1;
		$rows_per_page= $share;
		$page_start = ( $page - 1 ) * $rows_per_page;
		$page_end = $page;
		
    	$sql = "SELECT * FROM ".TBL_PRODUCT." p LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$langid' ".
				" LEFT JOIN ".TBL_PRODUCT_LANG." t ON t.product_id = p.product_id AND t.lang_id = '$langid' ".
				"WHERE t.product_name like '%%$txtKey%%' ORDER BY p.product_id ASC LIMIT $page_start, $rows_per_page";
    	$res = $db->get_results($sql);
    	return $res;
    }
	
	
	/**
     * Get a product with inputted product id.
     * @param $id id of the product.
     * @return a product object with inputted id.
     * Notes: product object contain fields is same as defined in database.  
     */
    function getProductDetail($db,$langid, $productid){
    	$sql = "SELECT p.*,FORMAT(p.price,0) as price,t.*, l.cat_name FROM ".TBL_PRODUCT." p ".
				"LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$langid' ".
				" LEFT JOIN ".TBL_PRODUCT_LANG." t ON t.product_id = p.product_id AND t.lang_id = '$langid' ".
				"WHERE p.product_id = '$productid' AND t.lang_id = '$langid' ";
    	$res = $db->get_row($sql);
    	return $res;
    }
	
	function getProductDetailFirstByCat($db,$langid, $catid){
    	$sql = "SELECT p.*,FORMAT(p.price,0) as price, l.cat_name,t.* FROM ".TBL_PRODUCT." p ".
				"LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
				" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$langid' ".
				" LEFT JOIN ".TBL_PRODUCT_LANG." t ON t.product_id = p.product_id AND t.lang_id = '$langid' ".
				"WHERE p.category_id='$catid' ORDER BY c.cat_ID LIMIT 0,1";
    	$res = $db->get_row($sql);
    	
    	return $res;
    }
	
	function getAllProductsByGroup($db,$langid,$catid){
		$strSQL = "SELECT p.product_image,t.product_name_img,t.short_info,t.product_name,t.product_id FROM ".TBL_PRODUCT." p ".
					"LEFT JOIN ".TBL_CATEGORY." c ON p.category_id = c.cat_ID ".
					" LEFT JOIN ".TBL_CATEGORY_LANG." l ON l.cat_id = c.cat_ID AND l.lang_id = '$langid' ".
					" LEFT JOIN ".TBL_PRODUCT_LANG." t ON t.product_id = p.product_id AND t.lang_id = '$langid' ".
					"WHERE p.category_id ='$catid'";
					"ORDER BY p.product_id ASC";
		$res = $db->get_results($strSQL);
		return $res;
	}
	
	 function PhotosByProductId($db, $id) {
    	$sql = "SELECT * FROM " . TBL_PRODUCT_PHOTOS . " p ".
				" WHERE p.product_id='$id'";
    			
    	$res = $db->get_results($sql);
    	return $res;
    }
	
}
?>